﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ModelLayer;

namespace DBLayer
{
    public class ShowDBClient : IShowDB
    {
        private static SqlCommand dbCmd = null;

        private IMovieDB movieDB = new MovieDBClient();
        private IRoomDB roomDB = new RoomDBClient();

        public Show GetShow(int id)
        {
            string sql = "SELECT * FROM Show WHERE id = " + id + ";";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Show show;

            if (dbReader.Read())
            {
                show = new Show(
                    Convert.ToInt32(dbReader["id"].ToString()),
                    movieDB.GetMovie(Convert.ToInt32(dbReader["movieID"].ToString())),
                    roomDB.GetRoom(Convert.ToInt32(dbReader["roomID"].ToString())),
                    Convert.ToDateTime(dbReader["datetime"].ToString()),
                    Convert.ToDecimal(dbReader["showPrice"].ToString()));
            }
            else
            {
                show = null;
            }

            dbReader.Close();
            dbCmd.Connection.Close();

            return show;
        }

        public List<Show> GetShows(Movie movie)
        {
            List<Show> returnList = new List<Show>();

            //string sql = "SELECT * FROM Show WHERE datetime >= CAST('" + dateTimeStart.ToString("yyyy'-'MM'-'dd") + "' AS smalldatetime) AND datetime < CAST('" + dateTimeEnd.ToString("yyyy'-'MM'-'dd") + "' AS smalldatetime);";
            string sql = "SELECT " +
            "Show.id, Show.datetime, Show.movieID, Show.roomID, Show.showPrice, " +
            "Movie.id AS movieID, Movie.active AS movieActive, Movie.title AS movieTitle, Movie.playLength AS moviePlayLength, Movie.actors AS movieActors, Movie.minAge AS movieMinAge, Movie.description AS movieDescription, Movie.rating AS movieRating, Movie.director AS movieDirector, Movie.genre AS movieGenre, Movie.language AS movieLanguage, Movie.price AS moviePrice, Movie.licencee AS movieLicencee, Movie.rentalDateStart AS movieRentalDateStart, Movie.rentalDateEnd AS movieRentalDateEnd, " +
            "Room.id AS roomID, Room.name AS roomName, Room.seats AS roomSeats, " +
            "Cinema.id AS cinemaID, Cinema.name AS cinemaName, Cinema.address AS cinemaAddress " +
            "FROM Show " +
            "JOIN Movie ON Show.movieID = Movie.id " +
            "JOIN Room ON Show.roomID = Room.id " +
            "JOIN Cinema ON Room.cinemaID = Cinema.id " +
            "WHERE Show.movieID = " + movie.Id + " " +
            "AND Show.datetime >= CAST(GETDATE() AS smalldatetime) " +
            "ORDER BY Show.datetime ASC;";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            while (dbReader.Read())
            {
                returnList.Add(
                    new Show(
                        Convert.ToInt32(dbReader["id"]),
                        new Movie(
                            Convert.ToInt32(dbReader["movieID"]),
                            Convert.ToBoolean(dbReader["movieActive"].ToString()),
                            dbReader["movieTitle"].ToString(),
                            Convert.ToInt32(dbReader["moviePlayLength"]),
                            dbReader["movieActors"].ToString(),
                            Convert.ToInt32(dbReader["movieMinAge"].ToString()),
                            dbReader["movieDescription"].ToString(),
                            dbReader["movieRating"].ToString(),
                            dbReader["movieDirector"].ToString(),
                            dbReader["movieGenre"].ToString(),
                            dbReader["movieLanguage"].ToString(),
                            Convert.ToDecimal(dbReader["moviePrice"].ToString()),
                            dbReader["movieLicencee"].ToString(),
                            Convert.ToDateTime(dbReader["movieRentalDateStart"]),
                            Convert.ToDateTime(dbReader["movieRentalDateEnd"])
                        ),
                    //movieDB.GetMovie(Convert.ToInt32(dbReader["movieID"].ToString())),
                        new Room(
                            Convert.ToInt32(dbReader["roomID"]),
                            dbReader["roomName"].ToString(),
                            Convert.ToInt32(dbReader["roomSeats"]),
                            new Cinema(
                                Convert.ToInt32(dbReader["cinemaID"]),
                                dbReader["cinemaName"].ToString(),
                                dbReader["cinemaAddress"].ToString()
                            )
                        ),
                    //roomDB.GetRoom(Convert.ToInt32(dbReader["roomID"].ToString())),
                        Convert.ToDateTime(dbReader["datetime"].ToString()),
                        Convert.ToDecimal(dbReader["showPrice"].ToString())
                    )
                );
            }
            dbReader.Close();
            dbCmd.Connection.Close();

            return returnList;
        }

        public List<Show> GetShows(DateTime dateTimeStart, DateTime dateTimeEnd)
        {
            List<Show> returnList = new List<Show>();

            //string sql = "SELECT * FROM Show WHERE datetime >= CAST('" + dateTimeStart.ToString("yyyy'-'MM'-'dd") + "' AS smalldatetime) AND datetime < CAST('" + dateTimeEnd.ToString("yyyy'-'MM'-'dd") + "' AS smalldatetime);";
            string sql = "SELECT " +
            "Show.id, Show.datetime, Show.movieID, Show.roomID, Show.showPrice, " + 
            "Movie.id AS movieID, Movie.active AS movieActive, Movie.title AS movieTitle, Movie.playLength AS moviePlayLength, Movie.actors AS movieActors, Movie.minAge AS movieMinAge, Movie.description AS movieDescription, Movie.rating AS movieRating, Movie.director AS movieDirector, Movie.genre AS movieGenre, Movie.language AS movieLanguage, Movie.price AS moviePrice, Movie.licencee AS movieLicencee, Movie.rentalDateStart AS movieRentalDateStart, Movie.rentalDateEnd AS movieRentalDateEnd, " +
            "Room.id AS roomID, Room.name AS roomName, Room.seats AS roomSeats, " +
            "Cinema.id AS cinemaID, Cinema.name AS cinemaName, Cinema.address AS cinemaAddress " +
            "FROM Show " +
            "JOIN Movie ON Show.movieID = Movie.id " +
            "JOIN Room ON Show.roomID = Room.id " +
            "JOIN Cinema ON Room.cinemaID = Cinema.id " +
            "WHERE Show.datetime >= CAST('" + dateTimeStart.ToString("yyyy'-'MM'-'dd' 'HH':'mm") + "' AS smalldatetime) " +
            "AND Show.datetime < CAST('" + dateTimeEnd.ToString("yyyy'-'MM'-'dd' 'HH':'mm") + "' AS smalldatetime) " +
            "ORDER BY Show.id ASC;";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            while (dbReader.Read())
            {
                returnList.Add(
                    new Show(
                        Convert.ToInt32(dbReader["id"]),
                        new Movie(
                            Convert.ToInt32(dbReader["movieID"]),
                            Convert.ToBoolean(dbReader["movieActive"].ToString()),
                            dbReader["movieTitle"].ToString(),
                            Convert.ToInt32(dbReader["moviePlayLength"]),
                            dbReader["movieActors"].ToString(),
                            Convert.ToInt32(dbReader["movieMinAge"].ToString()),
                            dbReader["movieDescription"].ToString(),
                            dbReader["movieRating"].ToString(),
                            dbReader["movieDirector"].ToString(),
                            dbReader["movieGenre"].ToString(),
                            dbReader["movieLanguage"].ToString(),
                            Convert.ToDecimal(dbReader["moviePrice"].ToString()),
                            dbReader["movieLicencee"].ToString(),
                            Convert.ToDateTime(dbReader["movieRentalDateStart"]),
                            Convert.ToDateTime(dbReader["movieRentalDateEnd"])
                        ),
                        //movieDB.GetMovie(Convert.ToInt32(dbReader["movieID"].ToString())),
                        new Room(
                            Convert.ToInt32(dbReader["roomID"]),
                            dbReader["roomName"].ToString(),
                            Convert.ToInt32(dbReader["roomSeats"]),
                            new Cinema(
                                Convert.ToInt32(dbReader["cinemaID"]),
                                dbReader["cinemaName"].ToString(),
                                dbReader["cinemaAddress"].ToString()
                            )
                        ),
                        //roomDB.GetRoom(Convert.ToInt32(dbReader["roomID"].ToString())),
                        Convert.ToDateTime(dbReader["datetime"].ToString()),
                        Convert.ToDecimal(dbReader["showPrice"].ToString())
                    )
                );
            }
            dbReader.Close();
            dbCmd.Connection.Close();

            return returnList;
        }

        public int InsertShow(Show s)
        {
            string sql = "INSERT INTO Show (datetime, movieID, roomID, showPrice) VALUES (CAST('" + s.DateTime.ToString("yyyy'-'MM'-'dd' 'HH':'mm") + "' AS smalldatetime), " + s.Movie.Id + ", " + s.Room.Id + ", " + s.ShowPrice + ");";
            return AccessDBSQLClient.ExecuteQuery(sql);
        }

        public int UpdateShow(Show s)
        {
            String sql = "UPDATE Show SET datetime ='" + s.DateTime + "', movieID = " + s.Movie.Id + ", roomID = " + s.Room.Id + ", showPrice = " + s.ShowPrice + ", WHERE id = " + s.Id + ";";
            return AccessDBSQLClient.ExecuteQuery(sql);
        }

        public int DeleteShow(int id)
        {
            string sql = "DELETE FROM Show WHERE id = " + id + ";";
            return AccessDBSQLClient.ExecuteQuery(sql);

        }

        //OBSOLETE
        public Show GetShow(DateTime dateTime)
        {
            string sql = "SELECT * FROM Show WHERE datetime = '" + dateTime + "';";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Show s;

            if (dbReader.Read())
            {
                s = new Show(
                    movieDB.GetMovie(Convert.ToInt32(dbReader["movieID"].ToString())),
                    roomDB.GetRoom(Convert.ToInt32(dbReader["roomID"].ToString())),
                    Convert.ToDateTime(dbReader["datetime"].ToString()),
                    Convert.ToDecimal(dbReader["showPrice"].ToString()));
            }
            else
            {
                s = null;
            }
            dbReader.Close();
            dbCmd.Connection.Close();

            return s;
        }

        public List<Show> FindShowsByDate(DateTime a)
        {
            List<Show> returnList = new List<Show>();

            string sql = "SELECT * FROM Show WHERE datetime = '" + a + "'";

            SqlCommand dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Show s;

            while (dbReader.Read())
            {
                s = new Show(
                    movieDB.GetMovie(Convert.ToInt32(dbReader["movieID"].ToString())),
                    roomDB.GetRoom(Convert.ToInt32(dbReader["roomID"].ToString())),
                    Convert.ToDateTime(dbReader["datetime"].ToString()),
                    Convert.ToDecimal(dbReader["showPrice"].ToString()));

                returnList.Add(s);
            }
            dbReader.Close();
            dbCmd.Connection.Close();

            return returnList;
        }
    }
}
